package jihyun51.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import jihyun.utils.OracleJDBC;
import jihyun51.model.MemberVO;

public class MemberDAO {

	private final String insertSQL = "insert into memberv1(mno, userid,pwd,name,gender) values (mbseq.nextval,?,?,?,?)";
	private final String updateSQL = "update memberv1 set name=?, pwd=?, regdate=sysdate where mno=?";
	private final String deleteSQL = "delete from memberv1 where mno=?";
	private final String selectSQL = "select  mno,userid,name,regdate from memberv1 order by mno desc";
	private final String selectOneSQL = "select * from memberv1 where mno=?";

	// 회원가입 insert
	public int insertMember(MemberVO m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, m.getUid());
			ps.setString(2, m.getPwd());
			ps.setString(3, m.getName());
			ps.setString(4, m.getGender());
			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}

	// 회원수정 update
	public int updateMember(MemberVO m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			conn = OracleJDBC.openConn();
			ps=conn.prepareStatement(updateSQL);
			ps.setString(1, m.getName());
			ps.setString(2, m.getPwd());
			ps.setString(3,m.getMno());

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}

	// 회원삭제 delete
	public int deleteMember(String mno) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
			// deleteSQL 문에 ?에 값을 넣어주기 위해서
			ps.setString(1, mno);
			cnt = ps.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}

	// 회원전체조회 select
	public ArrayList<MemberVO> selectMember() {
		ArrayList<MemberVO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();

			// **조회를 실행하면 resultset에 가져온 값을 ArrayList에 add 한다.**
			while (rs.next()) {
				MemberVO m = new MemberVO(rs.getString(1), rs.getString(2),
						null, rs.getString(3), null, rs.getString(4));
				list.add(m);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}

		return list;
	}

	// 회원상세조회 selectOne
	public MemberVO selectOneMember(String mno) {
		MemberVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			
			ps = conn.prepareStatement(selectOneSQL);
			ps.setString(1, mno);
			rs = ps.executeQuery();
	
			rs.next();
			m = new MemberVO(rs.getString(1), rs.getString(2), rs.getString(3),
					rs.getString(4), rs.getString(5), rs.getString(6));
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}

		return m;
	}
}
